import mysql from 'mysql'
import { strFormat } from './utils.js'
import { createForm } from './form.js'
import { createViewTemplate } from './template.js'
import { createValidate } from './validate.js'
import { createRestful } from './restful.js'
import { createRouter, appendTopRouter } from './router.js'
import { createStore, appendTopStore } from './store.js'
import { createComposable } from './composable.js'
import fs from 'fs'

export class Db {
  #conn
  #whereStr
  #orderStr
  /**
   *
   * @param {string} host
   * @param {string} user
   * @param {string} password
   * @param {string} database
   * @param {string} port
   */
  constructor (host, user, password, database, port) {
    this.#conn = mysql.createConnection({
      host: host,
      user: user,
      password: password,
      database: database,
      port: port
    })
    this.#conn.connect()
  }

  // db close
  end = () => {
    this.#conn.end()
  }

  query = (queryStr) => {
    const promise = new Promise((resolve, reject) => {
      this.#conn.query(queryStr, function (_err, rows) {
        resolve(rows)
      })
    })
    return promise
  }

  clean = () => {
    this.#whereStr = ''
    this.#orderStr = ''
  }

  /**
   * db select
   * @param {string} tablename
   * @param {object} fields select fields
   * @returns {promise}
   */
  select = (tableName, fields = []) => {
    const selFields = fields.length ? fields.join(',') : '*'
    let queryStr = `
    SELECT ${selFields} FROM ${tableName}
    `
    if (this.#whereStr !== '') {
      queryStr += ` where ${this.#whereStr}`
    }
    return this.query(queryStr)
  }

  /**
   * insert
   * @param {string} tableName
   * @param {object} data insert data
   */
  insert = (tableName, data) => {
    const columnFields = Object.keys(data)
    let fields = '('
    let values = '('
    const length = columnFields.length
    for (let i = 0; i < length; i++) {
      const field = columnFields[i]
      fields += `\`${field}\``
      values += `'${data[field]}'`
      if (i !== length - 1) {
        fields += ','
        values += ','
      }
      fields += ')'
      values += ')'
    }
    const queryStr = `
    INSERT INTO ${tableName} ${fields} VALUES ${values}
    `
    return this.query(queryStr)
  }

  /**
   * insert multi data
   * @param {string} tableName
   * @param {array} dataArr
   */
  inserts = (tableName, dataArr) => {
    let fields = '('
    let values = '('
    const arrLen = dataArr.length
    // field
    const keys = Object.keys(dataArr[0])
    const length = keys.length
    for (let i = 0; i < length; i++) {
      const field = keys[i]
      fields += `\`${field}\``
      values += `'${dataArr[0][field]}'`
      if (i !== length - 1) {
        fields += ','
        values += ','
      }
    }
    values += ')'
    fields += ')'
    // values
    for (let index = 1; index < arrLen; index++) {
      values += ',('
      const keys = Object.keys(dataArr[index])
      const length = keys.length
      for (let i = 0; i < length; i++) {
        const field = keys[i]
        values += `'${dataArr[index][field]}'`
        if (i !== length - 1) {
          values += ','
        }
      }
      values += ')'
    }
    const queryStr = `
    INSERT INTO ${tableName} ${fields} VALUES ${values}`
    return this.query(queryStr)
  }

  // where
  whereIn = (whereInObj) => {
    // TD
  }

  /**
   *  db where
   * @param {object} whereObj
   * @returns  this
   */
  where = (whereObj) => {
    // TD
    const keys = Object.keys(whereObj)
    const length = keys.length
    let str = ''
    for (let i = 0; i < length; i++) {
      str += `\`${keys[i]}\`= '${whereObj[keys[i]]}'`
      if (i !== length - 1) {
        str += ' and '
      }
    }
    this.#whereStr = this.#whereStr === '' ? str : `${this.#whereStr} and ${str}`
    return this
  }

  order = () => {
    // TD
  }
}

export function tableModel (tableName, op = '-all') {
  fs.readFile('src/scripts/config.json', 'utf8', (err, data) => {
    if (err) throw err
    const config = JSON.parse(data)
    // db connection
    const db = new Db(config.db.host, config.db.user, config.db.password, config.db.database, config.db.port)
    // const connection = conn(config.db.host, config.db.user, config.db.password, config.db.database, config.db.port)
    const queryStr = strFormat(config.db.tableStrucStr, `${config.db.tablePrefix + tableName}`)
    db.query(queryStr).then((rows) => {
      let tableDesc = ''
      if (rows.length > 0) {
        const columns = []
        rows.forEach((item) => {
          if (item.columnName === 'id') {
            tableDesc = item.columnComment
          }
          if (item.columnComment.indexOf('ignore') === -1 && config.db.exceptedFields.split(',').findIndex((except) => except === item.columnName) === -1) {
            columns.push(item)
          }
        })
        if (columns.length > 0) {
          switch (op) {
            case '-form':
              createForm(columns, tableName)
              break
            case '-view':
              createViewTemplate(columns, tableName, 'add')
              createViewTemplate(columns, tableName, 'update')
              createViewTemplate(columns, tableName, 'detail')
              break
            case '-validate':
              createValidate(columns, tableName)
              break
            case '-restful':
              createRestful(tableName)
              break
            case '-router':
              createRouter(tableName, tableDesc)
              appendTopRouter(tableName)
              break
            case '-store':
              createStore(tableName)
              appendTopStore(tableName)
              break
            case '-composable':
              createComposable(columns, tableName)
              break
            case '-all':
              createForm(columns, tableName)
              createComposable(columns, tableName)
              createViewTemplate(columns, tableName, 'add')
              createViewTemplate(columns, tableName, 'update')
              createViewTemplate(columns, tableName, 'detail')
              createValidate(columns, tableName)
              createRestful(tableName)
              createRouter(tableName, tableDesc)
              appendTopRouter(tableName)
              createStore(tableName)
              appendTopStore(tableName)
          }
        } else {
          console.log('============ no effective fields or the table is not existed ===============')
        }
      }
    })
    db.end()
  })
}
